
var { sqlCheckType, err_reply, success_reply, success_reply2 } = require('../types/type.js') // 引入枚举类型

var dbConfig = require('../../util/dbconfig.js') // 引入dbconfig

var timeExchange = require('../.././tools/timeExchange.js'); // 封装好的时间工具
const { array } = require('i/lib/util.js');
const { json } = require('express');

// SLQ例子
// (SELECT tbh.* FROM
// 	(SELECT tbg.*, COALESCE(tbg2.depart_name, '') AS parent_name FROM

// 	(SELECT tbf.*,t3.nick_name as create_name from 
// 	(SELECT tb.* FROM
// 	(SELECT t1.*, t2.nick_name as depart_manager_name FROM 
// 	`department` t1 INNER JOIN userInfo t2 on t1.depart_manager = t2.user_id
// 	UNION
// 	SELECT *, NULL as depart_manager_name FROM `department` WHERE depart_manager = '' OR ISNULL(depart_manager)) 
// 	AS tb) AS tbf
// 	INNER JOIN userInfo t3  ON tbf.creater_userid = t3.user_id) 

// 	AS tbg

// 	LEFT JOIN `department` AS tbg2 ON tbg.parent_id = tbg2.id)

// AS tbh

// WHERE tbh.create_name='管理员') ORDER BY id;


/** 通过2次内连接查询、1次左连接查询（INNER JOIN ON、LEFT JOIN ON），不仅返回了部门的所有数据，
   * 连带着部门经理user_id、创建人user_id也查询到了对应的真实姓名、上级部门名字，
   * 并以depart_manager_name、create_name、parent_name命名的三个新字段返回 
   * 
   * 第1次.内连接查询：depart_manager_name（部门经理名字）
   * 第2次.内连接查询：create_name（部门创建人名字）
   * 第3次.左连接查询：parent_name（上级部门名字）
   * 
   * */
  // 分页查询（部分SQL）
const sql_query_condition = `SELECT tbh.* FROM
	(SELECT tbg.*, COALESCE(tbg2.depart_name, '') AS parent_name FROM

	(SELECT tbf.*,t3.nick_name as create_name from 
	(SELECT tb.* FROM
	(SELECT t1.*, t2.nick_name as depart_manager_name FROM 
	${`department`} t1 INNER JOIN ${`userInfo`} t2 on t1.depart_manager = t2.user_id
	UNION
	SELECT *, NULL as depart_manager_name FROM ${`department`}  WHERE depart_manager = '' OR ISNULL(depart_manager)) 
	AS tb) AS tbf
	INNER JOIN ${`userInfo`} t3  ON tbf.creater_userid = t3.user_id) 

	AS tbg

	LEFT JOIN ${`department`}  AS tbg2 ON tbg.parent_id = tbg2.id)

AS tbh`

// 查询所有个数（部分SQL）
const sql_qurty_all = `SELECT count(1) FROM
(SELECT tbg.*, COALESCE(tbg2.depart_name, '') AS parent_name FROM

(SELECT tbf.*,t3.nick_name as create_name from 
(SELECT tb.* FROM
(SELECT t1.*, t2.nick_name as depart_manager_name FROM 
${`department`} t1 INNER JOIN ${`userInfo`} t2 on t1.depart_manager = t2.user_id
UNION
SELECT *, NULL as depart_manager_name FROM ${`department`}  WHERE depart_manager = '' OR ISNULL(depart_manager)) 
AS tb) AS tbf
INNER JOIN ${`userInfo`} t3  ON tbf.creater_userid = t3.user_id) 

AS tbg

LEFT JOIN ${`department`}  AS tbg2 ON tbg.parent_id = tbg2.id)

AS tbh`

// @description 部门查询（条件查询 | 分页）
const department_check = async (req, res) => {
  console.log('\n\ndepartment_check -----------')
// 由于查询条件「query.departManager」（部门经理）传过来的是名字，而部门表保存的部门经理是userID;
// 我们需要先通过名字去用户表(userInfo)拿到user_id,并作为where查询条件之一，然后再去部门表里查询数据

  const query_id = req.query.id // 部门ID
  const query_name = req.query.depart_name // 部门名字
  const query_manger = req.query.depart_manager_name // 部门领导名字
  const query_fuzzy = req.query.fuzzy // 模糊搜索

  const limit = Number(req.query.limit) ?? 1000 //限制一次查询多少条，默认1000条
  const pageIndex = Number(req.query.pageIndex) ?? 1 // 要查询第几页数据
  if (pageIndex < 0) pageIndex = 1
  const offset = (pageIndex - 1) * limit
  if (offset < 0) offset = 0
  
  console.log(req.query)
  
  var params = []
  // 字段值如果不是用？（占位符）表示，直接拼到sql语句上，那就需要要引号
  // 例如1："SELECT * FROM `department` WHERE depart_manager='李四'"
  // 例如2: "SELECT * FROM `department` WHERE depart_name='业务部' AND depart_manager in (SELECT user_id FROM userInfo WHERE nick_name ='李四')"
  // 复杂点，要求部门经理的名字也要查询
  // SELECT t1.*, t2.nick_name as depart_manager_name FROM `department` t1 INNER JOIN userInfo t2 on t1.depart_manager = t2.user_id WHERE depart_name='业务部' AND depart_manager in (SELECT user_id FROM userInfo WHERE nick_name ='李四');
  if (query_fuzzy) {
    console.log('模糊搜索')
    // 模糊搜索
    if (query_id)  params.push(['id', `'${query_id}%'`])
    if (query_name)  params.push(['depart_name', `'${query_name}%'`])
  } else {
    console.log('精准搜索')
    // 精准搜索
    if (query_id)  params.push(['id', `'${query_id}'`])
    if (query_name)  params.push(['depart_name', `'${query_name}'`])
  }
  if (query_manger) {
    // 部门表 depart_manager 存放的是部门经理的用户ID，
    const manager_uid = `(SELECT user_id FROM userInfo WHERE nick_name ='${query_manger}')`
    params.push(['depart_manager', manager_uid])
  }
  
  console.log('params打印看看: ' + JSON.stringify(params))

  // 如果params为空，就是‘查询所有’; 否则就是‘条件查询’
  // var sql_check = 'SELECT * FROM `department`'
  // var sql_check = 'SELECT t1.*, t2.nick_name as depart_manager_name FROM `department` t1 INNER JOIN userInfo t2 on t1.depart_manager = t2.user_id'
  
  /*** 这个查询，并没有返回‘创建人姓名’
   var sql_check = `SELECT tb.* FROM
  (SELECT t1.*, t2.nick_name as depart_manager_name FROM ${`department`} t1 INNER JOIN userInfo t2 on t1.depart_manager = t2.user_id 
  UNION SELECT *, NULL as depart_manager_name FROM ${`department`} WHERE depart_manager = '' OR ISNULL(depart_manager)) 
  AS tb `
   */

  /** 通过两次内连接查询（INNER JOIN on），不仅返回了部门的所有数据，
   * 连带着部门经理user_id、创建人user_id也查询到了对应的真实姓名，
   * 并以depart_manager_name、create_name两个新字段返回 */
  // var sql_query_condition = `SELECT tbg.* FROM

  // (SELECT tbf.*,t3.nick_name as create_name from 
  // (SELECT tb.* FROM
  // (SELECT t1.*, t2.nick_name as depart_manager_name FROM 
  // ${`department`} t1 INNER JOIN ${`userInfo`} t2 on t1.depart_manager = t2.user_id
  // UNION
  // SELECT *, NULL as depart_manager_name FROM ${`department`} WHERE depart_manager = '' OR ISNULL(depart_manager)) 
  // AS tb) AS tbf
  // INNER JOIN userInfo t3  ON tbf.creater_userid = t3.user_id) 
  
  // AS tbg `

  // var sql_qurty_all = `SELECT count(1) FROM

  // (SELECT tbf.*,t3.nick_name as create_name from 
  // (SELECT tb.* FROM
  // (SELECT t1.*, t2.nick_name as depart_manager_name FROM 
  // ${`department`} t1 INNER JOIN ${`userInfo`} t2 on t1.depart_manager = t2.user_id
  // UNION
  // SELECT *, NULL as depart_manager_name FROM ${`department`} WHERE depart_manager = '' OR ISNULL(depart_manager)) 
  // AS tb) AS tbf
  // INNER JOIN userInfo t3  ON tbf.creater_userid = t3.user_id) 
  
  // AS tbg `

  // 查询条件params可能为空，分页参数limit、offset也可能为空
  // 条件查询，分页
  const sql_query_condition_data = await check_plblic(sql_query_condition, false)
  // 如果是分页查询，那还需要查询符合情况的总个数
  // 比如数据库里有200条数据，光符合查询条件的数据就有50条，但是分页查询只能返回10条数据，这个50也要查询
  if (limit > 0 && offset >= 0) {
    // sql_qurty_all_data查询返回格式： [{"count(1)":2}]
    const sql_qurty_all_data = await check_plblic(sql_qurty_all, true)
    const count = sql_qurty_all_data[0]['count(1)']
    success_reply2('', res, '查询成功2！',sql_query_condition_data,count)
  }else {
    const count = sql_query_condition_data.length
    success_reply2('', res, '查询成功1！',sql_query_condition_data,count)
  }
  // @params sql_check：查询的SQL语句（非条件那部分）
  // params isConditionAll： 是否统计符合条件的总个数查询（'select count(1) xxx'）
  function check_plblic(sql_check,isConditionAll) {
    return new Promise((resolve) => {
      if(params.length > 0) {
        // console.log('条件查询，正在拼凑sql_check～～')
        sql_check = sql_check + ' WHERE'
        var condition = ' '
        for (let index = 0; index < params.length; index++) {
          const element = params[index];
          // 根据条件，得到 in、Like、=
          const accurate_fuzzy = query_fuzzy ? "LIKE" : "=" //模糊搜索还是精准搜索
          const linkChar = element[0] == "depart_manager" ? "in" : accurate_fuzzy
          // 拼接WHERE条件后面的SQL语句
          if(index == 0) condition = condition + `${element[0]} ${linkChar} ${element[1]}`
          else condition = condition + ' AND ' + `${element[0]} ${linkChar} ${element[1]}`
        }
        // 拼凑完整的SQL语句
        sql_check = sql_check + condition
      }
      // 根据部门ID排序
      sql_check = '(' + sql_check + ') ORDER BY id'
    
      if (!isConditionAll && limit > 0 && offset >= 0) {
        // 说明当前请求是分页查询，进入这里是为了查询符合条件的总数
        sql_check = sql_check + ` LIMIT ${limit} OFFSET ${offset};`
      }
    
      console.log('打印查询语句：' + sql_check)
    
      const sqlParamters = []
      
      var callBack = (err,data) => {
        // console.log("部门查询 -> err: " + err)
        // console.log("部门查询 -> data: " + JSON.stringify(data))
        if (err) {
          console.log('department/check 查询出错了 err is: ' + err)
          // mineCallBack(sqlCheckType.Error)
          err_reply('/department/check',res,err)
        } else {
          // if(data === null || data.length == 0){
          //   console.log('department/check 数据不存在，可以插入!')
          //   // mineCallBack(sqlCheckType.False)
          //   success_reply('/department/check',res,'查询结果为空！',[])
          // }else {
          //   console.log('department/check 数据已经存在!')
          //   // mineCallBack(sqlCheckType.True)
          //   success_reply('/department/check',res,'查询成功！',data)
          // }
          // success_reply('/department/check',res,'查询成功！',data)
          resolve(data)
        }
      }
      dbConfig.sqlConnect(sql_check, sqlParamters, callBack)
    })
  }
}

// @description 部门新增
const department_add = (req, res) => {

  console.log('\n\n部门新增请求，从body取出参数：\n' + JSON.stringify(req.body))//req.query.userID
  const depart_name = req.body.depart_name ?? '' // 新部门名
  
  let parent_id = req.body.parent_id // 上级部门ID
  if(!parent_id && parent_id === '') parent_id = 0

  const depart_manager = req.body.depart_manager // 部门经理ID
  const creat_userid = req.body.user_id ?? 0 // 创建者userID
  const creat_username = req.body.creater_username // 创建者用户名
  
  const tokenValue = req.headers['authorization']
  console.log('\n取出token： ' + JSON.stringify(tokenValue))

  // 请求成功，返回客户端
  const req_success_reply = (insertId) => {
    res.send({
      'code': 0,
      'msg': `该部门${depart_name}，新增成功`,
      'data': {
        'id': insertId ?? '',//depart_name,  ,
        'depart_name': depart_name,
        'creater_userid': creat_userid,
        'create_time': creat_username,
      }
    })
  }

  // 校验该部门是否已经注册过，返回sqlCheckType类型
  var check_callback = (result) => {
    if (result == sqlCheckType.Error) {
      // 响应客户端请求
      err_reply('/department/add',res,'部门新增请求失败，sql查询异常！')
      return
    }else if(result == sqlCheckType.True){
  
      // 响应客户端请求
      err_reply('/department/add',res,`${depart_name}，该部门已存在`)
      return
    }else if(result == sqlCheckType.False){
      // 没查到该数据，可以继续新增
    }
  
    // var timeExchange = require('../tools/timeExchange.js'); // 封装好的时间工具
    var times = timeExchange.timeString_3daysLater() // 拿到:当前时间、3天后的时间
    // console.log(`当前时间是: ${times.current}   三天后时间:${times.expire}` )
    var createTime = times.current // 获取当前时间，格式：2024-03-30 03:41:48
    // sql语句
    var sql_insert = "INSERT INTO `department`(depart_name,creater_userid,create_time,parent_id,depart_manager) VALUES (?, ?, ?, ?, ?)"
    //参数，替换sql里面的占位符？
    var sqlParamters = [depart_name, creat_userid, createTime, parent_id, depart_manager];
    var callBack = (err,data) => {
      console.log("err: " + err)
      console.log("data: " + JSON.stringify(data))
      // {"fieldCount":0,"affectedRows":1,"insertId":1033,"serverStatus":2,"warningCount":0,"message":"","protocol41":true,"changedRows":0}
      if (err) {
        console.log('/department/add 请求出错了')
        // 响应客户端请求
        err_reply('/department/add',res,`${depart_name}，该部门插入失败`)
        return;
      } else {
        console.log('/department/add 请求成功 -- 部门插入成功')
        if (!data.insertId || data.insertId.length == 0){
          console.log(`${depart_name}，该新增的部门ID为空`)
          // 响应客户端请求
          err_reply('/department/add',res,`${depart_name}，该部门请求异常`)
          return
        }
        // 响应客户端请求
        req_success_reply(data.insertId ?? '')
      }
    }
    dbConfig.sqlConnect(sql_insert, sqlParamters, callBack)
  }
  // 校验该部门是否已经存在，不存在则返回true
  check_if_exists('department','depart_name',depart_name,res,check_callback)
}

// @description 部门删除
const department_delete = (req, res) => {
  console.log('部门删除请求 ---')
console.log("req.body: " + JSON.stringify(req.body))

const arr = req.body.params
let condition = ""
arr.forEach(element => {
  const element_id = element.id.toString()
  if(condition.length === 0){
    condition = `${element_id}`
  } else {
    condition = `${condition},${element_id}`
  }
});
console.log('打印一下condition---' + condition)
const sql_delete = `DELETE FROM ${`department`} WHERE id in (${condition})`
console.log('打印一下sql_delete---' + sql_delete)

var callBack = async (err,data) => {
    console.log("err: " + err)
    console.log("data: " + JSON.stringify(data))
    // {"fieldCount":0,"affectedRows":1,"insertId":0,"serverStatus":2,"warningCount":0,"message":"","protocol41":true,"changedRows":0}
    if (err) {
      console.log('/department/delete 请求出错了')
      // 响应客户端请求
      err_reply('/department/delete',res,`${condition}，该部门删除失败`)
      return;
    } else {
      console.log(`/department/delete 请求成功 -- 部门${condition}删除成功`)
      const updateResult = await department_update_parentid(condition)
      if(updateResult){
        // 响应客户端请求
        success_reply('/dep',res,'删除成功',{})
      } else{
        err_reply('/department/delete',res,`parent_id更新失败`)
      }
    }
  }
  dbConfig.sqlConnect(sql_delete, [], callBack)
}
// 部门删除之后，要更新相关部门数据的parent_id
function department_update_parentid (parent_ids) {
  return new Promise((reslove) => {
    const sql_update = `UPDATE ${`department`} SET parent_id=NULL WHERE parent_id in (${parent_ids})`;
    dbConfig.sqlConnect(sql_update, [], (err,data) => {
      if (err) {
        console.log('parent_id 更新请求出错了' + JSON.stringify(err))
        // 响应客户端请求
        // err_reply('/department/delete',res,`${condition}，parent_id 更新请求出错了`)
        reslove(false)
      } else {
        // console.log(`/department/delete 请求成功 -- 部门${condition}删除成功`)
        // // 响应客户端请求
        // success_reply('/dep',res,'删除成功',{})
        reslove(true)
      }
    })
  })
}

// @description 部门更新
const department_update = (req, res) =>  {

  console.log('\n\n部门更新请求，从body取出参数：\n' + JSON.stringify(req.body))//req.query.userID
  const depart_name = req.body.depart_name ?? '' // 部门名
  const depart_manager_id = req.body.depart_manager ?? '' // 部门经理ID
  const depart_id = req.body.id ?? '' // 部门ID
  const parent_id = Number(req.body.parent_id) ?? null // 上级部门ID
  
  const tokenValue = req.headers['authorization']
  console.log('\n取出token： ' + JSON.stringify(tokenValue))

  // var timeExchange = require('../tools/timeExchange.js'); // 封装好的时间工具
  var times = timeExchange.timeString_3daysLater() // 拿到:当前时间、3天后的时间
  // console.log(`当前时间是: ${times.current}   三天后时间:${times.expire}` )
  var updateTime = times.current // 获取当前时间，格式：2024-03-30 03:41:48
  // sql语句
  var sql_update = "UPDATE `department` SET depart_name=?, depart_manager=?, update_time=?, parent_id=? WHERE id=?"
  //参数，替换sql里面的占位符？
  var sqlParamters = [depart_name,depart_manager_id,updateTime, parent_id, depart_id];

  var callBack = (err,data) => {
    console.log("err: " + err)
    console.log("data: " + JSON.stringify(data))
    // {"fieldCount":0,"affectedRows":1,"insertId":1033,"serverStatus":2,"warningCount":0,"message":"","protocol41":true,"changedRows":0}
    if (err) {
      console.log('/department/update 请求出错了')
      // 响应客户端请求
      err_reply('/department/update',res,`${depart_name}，该部更新失败`)
      return;
    } else {
      console.log('/department/update 请求成功 -- 部门更新成功')
      console.log(data.affectedRows)
      console.log(typeof data.affectedRows)
      if (!data.affectedRows || data.affectedRows == 0){
        console.log(`${depart_name}，该新增的部门ID为空`)
        // 响应客户端请求
        err_reply('/department/update',res,`${depart_name}，该部门请求异常`)
        return
      }
      // 响应客户端请求
      success_reply('/department/update',res,'部门更新成功',data)
    }
  }
  dbConfig.sqlConnect(sql_update, sqlParamters, callBack)
}

/** 验证数据库是否存在这条数据（抽离 - 公共方法）
 *  （select * from `tableName` where fieldName=fieldValue）
 * @param tableName     要查询‘哪张表’
 * @param fieldName     查询条件，‘哪个字段 ’
 * @param fieldValue    fieldName ‘字段值’
 * @param mineCallBack  回调函数
 */
function check_if_exists(tableName, fieldName,fieldValue,res,mineCallBack){

  const msg = `查询条件 -> fieldName:${fieldName}  fieldValue:${fieldValue}  tableName:${tableName}`
  console.log('\n查询的tableName是：' + tableName)
  console.log('\n查询的fieldName是：' + fieldName)
  console.log('\n查询的fieldValue是：' + fieldValue)

  // var sql_check = "SELECT * FROM `"+ tableName +"` WHERE ?=?"
  // var sqlParamters = [tableName,fieldName,fieldValue]; //参数，替换sql里面的占位符？

  // 不正确
  // var sql_check = "SELECT * FROM `"+ tableName +"` WHERE depart_name=?"
  // var sqlParamters = [tableName,fieldValue]; //参数，替换sql里面的占位符？
  
  // 正确
  // var sql_check = "SELECT * FROM `department` WHERE depart_name=?"
  // var sqlParamters = [fieldValue]; //参数，替换sql里面的占位符？

  // 
  var spl = '`'
  var sql_check = `SELECT * FROM ${spl}${tableName}${spl} WHERE depart_name=?`
  var sqlParamters = [fieldValue]; //参数，替换sql里面的占位符？

  var callBack = (err,data) => {
    console.log("check_if_exists -> err: " + err)
    console.log("check_if_exists -> data: " + JSON.stringify(data))
    if (err) {
      console.log('查询出错了 err is: ' + err)
      mineCallBack(sqlCheckType.Error)
    } else {
      if(data === null || data.length == 0){
        console.log('数据不存在，可以插入!')
        mineCallBack(sqlCheckType.False)
      }else {
        console.log('数据已经存在!')
        mineCallBack(sqlCheckType.True)
      }
      
    }
  }
  dbConfig.sqlConnect(sql_check, sqlParamters, callBack)
}


module.exports = { department_add, department_check, department_delete, department_update }